package com.example.springbootlucenedemo.service.impl.dao;

import com.example.springbootlucenedemo.bean.TbMission;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TbMissionDao extends JpaRepository<TbMission,Long> {
    @Query(nativeQuery = true,value = "select tm.mission_id \n" +
            "from  tb_mission tm \n" +
            " WHERE tm.is_deleted = 0  and tm.is_second = 0  limit  ?1 OFFSET  ?2")
    List<Long> listTbMissionIdByother(int limit, int offset);
    @Query(nativeQuery = true,value = "select  count(*) \n" +
            "from  tb_mission tm \n" +
            "left join tb_district o on o.district_id = tm.district_id\n" +
            "left join tb_region d on  d.region_id = tm.region_id\n" +
            " WHERE tm.is_deleted = 0  and tm.is_second = 0")
    long countListTbMissionByother();

    @Query(nativeQuery = true,value = "select distinct tm.mission_id ,tm.detail_info ,tm.subject ,tm.create_datetime , tm.state ,tm.mission_type ,tm.browse_count ,\n" +
            " if ((tm.parent_child_exper = 1 or tm.is_mid_student=1),1,0 ) as is_parent_chile,\n" +
            " if  (apply.mission_id is null ,1,0) as is_apply, "+
            " \td.perm_code as rperm_code,\n" +
            "  o.perm_code as perm_code,\n" +
            "  tm.start_date \n" +
            "from  tb_mission tm \n" +
            "left join tb_district o on o.district_id = tm.district_id\n" +
            "left join tb_region d on  d.region_id = tm.region_id\n" +
            "left join (SELECT t.mission_id from tb_mission_personal m inner join tb_mission t  where t.mission_id=m.mission_id and m.selection in (1,4) and t.apply_deadline > now() ) apply on apply.mission_id =tm.mission_id \n"+
            " WHERE tm.is_deleted = 0  and tm.is_second = 0 and tm.mission_id = ?1 ")
    TbMission findTbMissionByMission_id(Long mission_id);
}
